package com.altocloud.parse;

import java.io.BufferedReader;
import java.io.DataInputStream;
import java.io.FileInputStream;
import java.io.InputStreamReader;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.sql.Timestamp;
import java.text.SimpleDateFormat;

import com.altocloud.database.DbUtil;
import com.altocloud.level2.entities.StationDetails;
import com.altocloud.level2.entities.StationDetailsExtra;
import com.altocloud.level2.entities.Weather;

public class Level2 {

	double parseDoubleValue(String str) {
		Double temp = 0.0;
		try {
			temp = Double.parseDouble(str);
		} catch (NumberFormatException E) {
			// E.printStackTrace();
			temp = -1.0;
		}
		return temp;
	}

	public static void main(String[] args) {
		// TODO Auto-generated method stub
		// new Level1().parseTblFile();
		// new Level2().parseCsvFile();
		// new Level2().parseOutFile();
	}

	DbUtil db = new DbUtil();
	Connection conn = db.getDbConnection("226-Level-2");
	Statement stmt = null;
	ResultSet rs = null;
	String sql = "";
	String sql1 = "";
	String sqlupdate = "";
	String sql1update = "";
	String temp = null;
	PreparedStatement pst = null;

	int parseMnetValue(String mnet) {
		int temp = 0;
		try {

			double temp1 = Double.parseDouble(mnet);
			temp = (int) temp1;
		} catch (Exception E) {
			// E.printStackTrace();
			temp = 0;
		}
		return temp;
	}

	public void parseCsvFile(String path) {
		try {
			FileInputStream fstream = new FileInputStream(path);

			// Get the object of DataInputStream
			DataInputStream in = new DataInputStream(fstream);
			BufferedReader br = new BufferedReader(new InputStreamReader(in));

			String strLine = "";
			// Read File Line By Line
			strLine = br.readLine();

			// Read File Line By Line
			String data[] = new String[22];
			// StationDetailsExtraQuery query = new StationDetailsExtraQuery();
			// StationDetailsQuery sq = new StationDetailsQuery();
			stmt = conn.createStatement();
			while ((strLine = br.readLine()) != null) {

				// System.out.println("" + strLine);
				data = strLine.split(",");
				// System.out.println("hUnGrY ReNiSh" + data.length);

				// 0-1-2 // primary id,secondary id,station name,
				// 3,4,5 //state,country,latitude,
				// 6,7,8 //longitude,elevation,mesowest network id,
				// 9,10,11 //network name,status,primary provider id,
				// 12,13,14 //primary provider,secondary provider
				// id,secondaryprovider,
				// 15,16,17 //tertiary provider id,tertiary provider,wims_id;

				// KIYA,,Abbeville Chris Crusta Memorial
				// Airport,LA,US,29.97578,-92.08422,
				// 4.9,1,NWS/FAA,ACTIVE,2,National Weather Service,,,,,;
				// Level-2 Weather Domain

				StationDetailsExtra detailsExtra = new StationDetailsExtra();
				StationDetails stationDetails = new StationDetails();
				// detailsExtra.setStnSecId(data[1]);
				stationDetails.setStationCode(data[0]);
				stationDetails.setOtherId(data[1]);
				stationDetails.setStationName(data[2]);
				stationDetails.setState(data[3]);
				stationDetails.setCountry(data[4]);
				try {
					stationDetails.setLatitude(Math
							.round(parseDoubleValue(data[5]) * 100.0) / 100.0);
					// System.out.println("Latitude is : " +
					// detailsExtra.getLatitude());
					stationDetails.setLongitude(Math
							.round(parseDoubleValue(data[6]) * 100.0) / 100.0);
					stationDetails.setElevation(Double.valueOf(data[7]));
				} catch (Exception e) {
					System.out.println("exception in double conversion");
					e.printStackTrace();
				}
				stationDetails.setMnet(parseMnetValue(data[8]));
				stationDetails.setNetworkName(data[9]);
				if (data[11].length() != 0)
					detailsExtra.setPrimaryProviderId(Integer
							.parseInt(data[11]));
				if (data[12].length() != 0)
					detailsExtra.setPrimaryProvider(data[12]);

				if (data[13].length() != 0) {
					detailsExtra.setSecondaryProviderId(Integer
							.parseInt(data[13]));
				}
				if (data[14].length() != 0) {
					detailsExtra.setSecondaryProvider(data[14]);
				}
				if (data[15].length() != 0) {
					detailsExtra.setTertiaryProviderId(Integer
							.parseInt(data[15]));
				}
				if (data[16].length() != 0) {
					detailsExtra.setTertiaryProvider(data[16]);
				}

				temp = "select count(*) from station_details where stn=? and stn_name=?";
				pst = conn.prepareStatement(temp);
				pst.setString(1, stationDetails.getStationCode());
				pst.setString(2, stationDetails.getStationName());
				ResultSet count = pst.executeQuery();

				// sql =
				// "select count(*) from station_details where stn='"+stationDetails.getStationCode()+"' and stn_name ='"+stationDetails.getStationName()+"'";
				// System.out.println(sql);
				// ResultSet count = stmt.executeQuery(sql);
				count.next();
				int no = count.getInt(1);
				if (no == 0) {
					sql = "insert into station_details values(?,?,?,?,?,?,?,?,?,?)";
					pst = conn.prepareStatement(sql);
					pst.setString(1, stationDetails.getStationCode());
					pst.setString(2, stationDetails.getStationName());
					pst.setString(3, stationDetails.getCountry());
					pst.setDouble(4, (double) stationDetails.getElevation());
					pst.setDouble(5, (double) stationDetails.getLatitude());
					pst.setDouble(6, stationDetails.getLongitude());
					pst.setInt(7, stationDetails.getMnet());
					pst.setString(8, stationDetails.getNetworkName());
					pst.setString(9, stationDetails.getOtherId());
					pst.setString(10, stationDetails.getState());
					pst.executeUpdate();

					sql = "insert into station_details_extra values(?,?,?,?,?,?,?,?,?)";
					pst = conn.prepareStatement(sql);
					pst.setString(1, detailsExtra.getPrimaryProvider());
					pst.setInt(2, detailsExtra.getPrimaryProviderId());
					pst.setString(3, detailsExtra.getSecondaryProvider());
					pst.setInt(4, detailsExtra.getSecondaryProviderId());
					pst.setString(5, detailsExtra.getStatus());
					pst.setString(6, detailsExtra.getTertiaryProvider());
					pst.setInt(7, detailsExtra.getTertiaryProviderId());
					pst.setString(8, stationDetails.getStationCode());
					pst.setString(9, stationDetails.getStationName());
					pst.executeUpdate();
				}

			}
			System.out.println("Station Extra details stored");
			// Close the input stream

			in.close();
		} catch (Exception e) {

			System.out.println("Exception: " + e);
			e.printStackTrace();
		}

	}

	public void parseOutFile(String path) {
		try {
			FileInputStream fstream = new FileInputStream(path);

			// Get the object of DataInputStream
			DataInputStream in = new DataInputStream(fstream);
			BufferedReader br = new BufferedReader(new InputStreamReader(in));

			String strLine;
			String pattern = "\\s{1,}";

			// Read File Line By Line
			for (int i = 0; i < 4; i++) {
				strLine = br.readLine();
			}

			// Read File Line By Line
			String data[] = null;
			String stationName = "";
			while ((strLine = br.readLine()) != null) {
				stationName = "";
				strLine = strLine.replaceAll(pattern, "|");
				// System.out.println("" + strLine);
				data = strLine.split("\\|");
				// System.out.println("hUnGrY ReNiSh" + data.length);
				// PARM =
				// MNET;SLAT;SLON;SELV;TMPF;SKNT;DRCT;GUST;PMSL;ALTI;DWPF;RELH;WTHR;P24I
				// |BULLF|20121003/0215|8.00|37.52|-110.73|1128.00|78.70|2.74|264.20|3.39|-9999.00|-9999.00|46.41|32.09|-9999.00|-9999.00

				// Level-2 Weather Domain
				// System.out.println(data.length);
				if (data.length == 17) {
					Weather weather = new Weather();
					weather.setStationCode(data[1]);
					// sq.findById(data[1]);
					final String INPUT1 = "yyyyMMdd/HHmm";
					final String OUTPUT1 = "yyyy-MM-dd HH.mm";
					String in1 = data[2];
					java.util.Date date = (new SimpleDateFormat(INPUT1))
							.parse(in1);
					Timestamp t = new Timestamp(date.getTime());
					weather.setTimestamp(t);
					// System.out.println(call);

					// weather.setMNET(parseMnetValue(data[3]));
					// weather.setSLAT(parseDoubleValue(data[4]));
					// weather.setSLON(parseDoubleValue(data[5]));
					// weather.setSELV(parseDoubleValue(data[6]));

					weather.setTMPF(parseDoubleValue(data[7]));
					weather.setSKNT(parseDoubleValue(data[8]));
					weather.setDRCT(parseDoubleValue(data[9]));
					weather.setGUST(parseDoubleValue(data[10]));
					weather.setPMSL(parseDoubleValue(data[11]));
					weather.setALTI(parseDoubleValue(data[12]));
					weather.setDWPF(parseDoubleValue(data[13]));
					weather.setRELH(parseDoubleValue(data[14]));
					weather.setWTHR(parseDoubleValue(data[15]));
					weather.setP24I(parseDoubleValue(data[16]));

					// System.out.println("Weather data stored");
					// wq.add(weather);

					sql = "insert into weather (alti, drct, dwpf, gust, p24i,pmsl, relh, sknt, tmpf, wthr, date, stn_id_stn)"
							+ " values("
							+ weather.getALTI()
							+ ","
							+ weather.getDRCT()
							+ ","
							+ weather.getDWPF()
							+ ","
							+ weather.getGUST()
							+ ","
							+ weather.getP24I()
							+ ","
							+ weather.getPMSL()
							+ ","
							+ weather.getRELH()
							+ ","
							+ weather.getSKNT()
							+ ","
							+ weather.getTMPF()
							+ ","
							+ weather.getWTHR()
							+ ",'"
							+ weather.getTimestamp()
							+ "','"
							+ weather.getStationCode() + "')";

					stmt = conn.createStatement();

					// System.out.println("sql is: " + sql);

					stmt.executeUpdate(sql);

				}

				else
					continue;
				// Close the input stream
			}
			in.close();
		} catch (Exception e) {
			e.printStackTrace();
		}

	}

}
